package com.newture.lxz.util;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;

import java.text.SimpleDateFormat;

public class ExcelKit {

    /**
     * excel右侧显示的行号比实际索引号大1
     * @param rowIndex
     * @returns {*}
     */
    public static int getRowIndexExplicit(int rowIndex) {
        return rowIndex + 1;
    }

    /** 根据列的序列数得到字符编码的顺序 */
    public static String getCharacterSeq(int number) {
        number += 1; // 修改成从0开始，也就是A->0
        String s = "";
        while (number > 0) {
            int m = number % 26;
            if (m == 0) {
                m = 26;
            }
            s = (char)(m + 64) + s;
            number = (number - m) / 26;
        }
        return s;
    }



    /**
     * 参考二进制转10进制 abcd(二进制) = d*2^0 + c*2^1 + b*2^2 + a*2^3
     */
    public static int getNumberFromCharacterSeq(String colStr) {
        int num = 0;
        int result = 0;
        int colStrLength = colStr.toCharArray().length;
        for(int i = 0; i < colStrLength; i++) {
            char ch = colStr.charAt(colStrLength - i - 1);
            num = (int)ch - (int)'A' + 1 ;
            num *= Math.pow(26, i);
            result += num;
        }
        //console.log("result - 1", result - 1)
        return result - 1;// 修改成从0开始，也就是A->0
    }


    /**
     * 安全的获取cell中的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        String cellValue;
        // 以下是判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: // 数字
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}
